--------------------------------------------------------------------------------
-- @name: rebuild and recreate
-- @author: dion cho
-- @note: test case for the difference between rebuild offline, reubild online and recreate
--------------------------------------------------------------------------------

@capture_on
set echo on
set pages 10000
set lines 200
set timing off
set trimspool on


-- when t1 has 3 columns
drop table t1 purge;

create table t1(c1 number, c2 number, c3 number);

insert into t1
select
  level as c1,
  level as c2,
  level as c3
from dual
connect by level <= 10000;


-- explan plans
explain plan for 
create index t1_n1 on t1(c1);

select * from table(dbms_xplan.display);

create index t1_n1 on t1(c1);

explain plan for 
alter index t1_n1 rebuild;

select * from table(dbms_xplan.display);

explain plan for 
alter index t1_n1 rebuild online;

select * from table(dbms_xplan.display);

alter index t1_n1 unusable;

explain plan for
alter index t1_n1 rebuild;

select * from table(dbms_xplan.display);


-- when t1 has 2 columns
drop table t1 purge;

create table t1(c1 number, c2 number);

insert into t1
select
  level as c1,
  level as c2
from dual
connect by level <= 10000;


-- explan plans
explain plan for 
create index t1_n1 on t1(c1);

select * from table(dbms_xplan.display);

create index t1_n1 on t1(c1);

explain plan for 
alter index t1_n1 rebuild;

select * from table(dbms_xplan.display);

explain plan for 
alter index t1_n1 rebuild online;

select * from table(dbms_xplan.display);


select segment_name, bytes 
from dba_segments
where segment_name in ('T1', 'T1_N1') and owner = user;



@capture_off
